package cn.newtest.bi.collection.service.impl;

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.risesoft.risebi.collection.dao.CatalogDao;
import net.risesoft.risebi.collection.model.Catalog;
import net.risesoft.risebi.collection.model.DbColumn;
import net.risesoft.risebi.collection.service.CatalogManager;
import net.risesoft.risebi.collection.service.DbMetaDataManager;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.appfuse.dao.Dao;
import org.appfuse.service.impl.BaseManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * 
 * @author <a href="mailto: wangchui@risesoft.net">王锤</a>
 * @version $Revision$
 * 
 */
@Service(value = "catalogManager")
@Transactional(rollbackFor = { java.lang.Exception.class })
public class CatalogManagerImpl extends BaseManager implements CatalogManager {
	private static Log log = LogFactory.getLog(CatalogManagerImpl.class);

	public List<Catalog> getAllOrderByTabIndex() throws Exception {
		return catalogDao.getAllOrderByTabIndex();
	}

	private CatalogDao catalogDao;
	private DbMetaDataManager dbMetaDataManager;

	public Dao getDao() {
		return getCatalogDao();
	}

	public CatalogDao getCatalogDao() {
		return catalogDao;
	}

	@Autowired
	public void setCatalogDao(CatalogDao catalogDao) {
		this.catalogDao = catalogDao;
	}

	@Autowired
	public void setDbMetaDataManager(DbMetaDataManager dbMetaDataManager) {
		this.dbMetaDataManager = dbMetaDataManager;
	}
	
	/**
	 * @see net.risesoft.risebi.collection.service.CatalogManager#getCatalogWithSo()
	 */
	public List getCatalogWithSo() throws Exception {
		return catalogDao.getCatalogWithSo();
	}
	
	public List getCatalogWithSoByApp(String appGuid, boolean includeVirtual) throws Exception {
		return catalogDao.getCatalogWithSoByApp(appGuid, includeVirtual);
	}
	
	public List<Catalog> getCatalogByApp(String appGuid) throws Exception {
		return this.getCatalogByApp(appGuid, null);
	}
	
	public List<Catalog> getCatalogByApp(String appGuid,String[] filter) throws Exception {
		return catalogDao.getCatalogByApp(appGuid, filter);
	}

	public List<Catalog> getTablesByTemplateGuid(String templateGuid) throws Exception {
		String hql = "from Catalog c where c.template.guid='" + templateGuid + "' and (c.type=2 or c.type=3)";
		
		List<Catalog> list = this.find(hql);		
		return list;
	}
	
	public List<Catalog> getCatalogWithSoByService(String serviceGuid) throws Exception {
		String hql = "from Catalog c where c.service.guid='" + serviceGuid + "' and c.type=1 and (c.virtual is null or c.virtual=false) and c.enabled=true";
		
		List<Catalog> list = this.find(hql);		
		return list;
	}
	
	/**
	 * @see net.risesoft.risebi.collection.service.CatalogManager#getCatalogWithSoByIds(java.lang.String[])
	 */
	public List getCatalogWithSoByIds(String[] catalogIdArray) throws Exception {
		return catalogDao.getCatalogWithSoByIds(catalogIdArray);
	}

	public List<Catalog> getCatalogByType(int type) throws Exception {
		return catalogDao.getCatalogByType(type);
	}

	public Map getPagedCatalog(Catalog catalog, int start, int limit)
			throws Exception {
		String hql = this.getSqlFindObj(catalog, "c");
		String countHql = "select count(*) " + hql;
		Integer count = this.getCount(countHql);

		// String orderByHql = hql + " order by u." + orderByField + " "
		// +orderBy;

		List list = this.find(hql, start, limit);
		Map map = new HashMap();
		map.put("count", count);
		map.put("list", list);
		return map;
	}

	public String buildFilterConfig(String tableName) throws Exception {
    	StringBuilder sb = new StringBuilder();
    	String config = "";
		List<DbColumn> lst = dbMetaDataManager.listAllColumns(tableName);
		for(DbColumn d : lst){
			String column_name = d.getColumn_name().trim().toLowerCase();
			String column_caption = d.getComment().trim().toLowerCase();

			if(column_name.endsWith("guid")){
				continue;
			}
			
			switch(d.getData_type()){
			//整型
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.BIGINT:
				sb.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'int'},");
				break;
			//浮点型
			case Types.FLOAT:
			case Types.REAL:
			case Types.DOUBLE:
			case Types.NUMERIC:
			case Types.DECIMAL:
				sb.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'float'},");
				break;
			//字符串
			case Types.CHAR:
			case Types.VARCHAR:
//				case Types.NCHAR:
//				case Types.NVARCHAR:
				sb.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'string'},");
				break;
			case Types.LONGVARCHAR:
//				case Types.LONGNVARCHAR:
				break;
			//BOOLEAN
			case Types.BIT:
			case Types.BOOLEAN:
				sb.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'string'},");
				break;
			//日期型
			case Types.DATE:
				sb.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'date', format: 'Y-m-d H:i:s'},");
				break;
			case Types.TIME:	
				sb.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'date', format: 'Y-m-d H:i:s'},");
				break;
			case Types.TIMESTAMP:
				sb.append("{id: '" + column_name + "', label: '" + column_caption + "', type: 'date', format: 'Y-m-d H:i:s'},");
				break;
			//blob
			case Types.BLOB:
				break;
			//clob
			case Types.CLOB:
				break;
			default:
			}
		}		
		
		config = sb.toString();
		if(config.endsWith(",")){
			config = config.substring(0, config.length()-1);
		}
		
		return config;
    }
	
	public String buildStoreFieldsConfig(String tableName) throws Exception {
    	StringBuilder sb = new StringBuilder();
    	String config = "";
		List<DbColumn> lst = dbMetaDataManager.listAllColumns(tableName);
		for(DbColumn d : lst){
			String column_name = d.getColumn_name().trim().toLowerCase();
			String column_caption = d.getComment().trim().toLowerCase();

			switch(d.getData_type()){
			//整型
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.BIGINT:
				sb.append("\r\n{name: '" + column_name + "',type: 'int'},");
				break;
			//浮点型
			case Types.FLOAT:
			case Types.REAL:
			case Types.DOUBLE:
			case Types.NUMERIC:
			case Types.DECIMAL:
				sb.append("\r\n{name: '" + column_name + "',type: 'float'},");
				break;
			//字符串
			case Types.CHAR:
			case Types.VARCHAR:
//			case Types.NCHAR:
//			case Types.NVARCHAR:
				sb.append("\r\n{name: '" + column_name + "',type: 'string'},");
				break;
			case Types.LONGVARCHAR:
//			case Types.LONGNVARCHAR:
				break;
			//BOOLEAN
			case Types.BIT:
			case Types.BOOLEAN:
				sb.append("\r\n{name: '" + column_name + "',type: 'boolean'},");
				break;
			//日期型
			case Types.DATE:
				sb.append("\r\n{name: '" + column_name + "',type: 'date',dateFormat: 'Y-m-d H:i:s'},");
				break;
			case Types.TIME:	
				sb.append("{name: '" + column_name + "',type: 'date',dateFormat: 'H:i:s'},");
				break;
			case Types.TIMESTAMP:
				sb.append("\r\n{name: '" + column_name + "',type: 'date',dateFormat: 'Y-m-d H:i:s'},");
				break;
			//blob
			case Types.BLOB:
				break;
			//clob
			case Types.CLOB:
				break;
			}
		}		

		config = sb.toString();
		if(config.endsWith(",")){
			config = config.substring(0, config.length()-1);
		}
		
		return config;
    }
	
	public String buildStoreFieldsCreateConfig(String tableName) throws Exception {
    	StringBuilder sb = new StringBuilder();
    	String config = "";
		List<DbColumn> lst = dbMetaDataManager.listAllColumns(tableName);
		for(DbColumn d : lst){
			String column_name = d.getColumn_name().trim().toLowerCase();
			switch(d.getData_type()){
			//整型
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.BIGINT:
				sb.append("\r\n'" + column_name + "': 0,");
				break;
			//浮点型
			case Types.FLOAT:
			case Types.REAL:
			case Types.DOUBLE:
			case Types.NUMERIC:
			case Types.DECIMAL:
				sb.append("\r\n'" + column_name + "': 0,");
				break;
			//字符串
			case Types.CHAR:
			case Types.VARCHAR:
//			case Types.NCHAR:
//			case Types.NVARCHAR:
				sb.append("\r\n'" + column_name + "': '',");
				break;
			case Types.LONGVARCHAR:
//			case Types.LONGNVARCHAR:
				break;
			//BOOLEAN
			case Types.BIT:
			case Types.BOOLEAN:
				sb.append("\r\n'" + column_name + "': true,");
				break;
			//日期型
			case Types.DATE:
				sb.append("\r\n'" + column_name + "': null,");
				break;
			case Types.TIME:	
				sb.append("\r\n'" + column_name + "':null,");
				break;
			case Types.TIMESTAMP:
				sb.append("\r\n'" + column_name + "':null,");
				break;
			//blob
			case Types.BLOB:
				break;
			//clob
			case Types.CLOB:
				break;
			}
		}		

		config = sb.toString();
		if(config.endsWith(",")){
			config = config.substring(0, config.length()-1);
		}
		
		return "{" + config + "}";
    }
	
	public String buildGridColumnsConfig(String tableName) throws Exception {
    	StringBuilder sb = new StringBuilder();
    	String config = "";

		List<DbColumn> lst = dbMetaDataManager.listAllColumns(tableName);
		for(DbColumn d : lst){
			String column_name = d.getColumn_name().trim().toLowerCase();
			String column_caption = d.getComment().trim().toLowerCase();
			
			boolean hidden = false;
			if(column_name.equals("item_guid")
				|| column_name.equals("item_parentguid")
				|| column_name.equals("catalog_guid")
				|| column_name.equals("reportinstance_guid")
			){
				hidden = true;
			}
			
			switch(d.getData_type()){
			//整型
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.BIGINT:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "'},");
				break;
			//浮点型
			case Types.FLOAT:
			case Types.REAL:
			case Types.DOUBLE:
			case Types.NUMERIC:
			case Types.DECIMAL:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "'},");
				break;
			//字符串
			case Types.CHAR:
			case Types.VARCHAR:
//			case Types.NCHAR:
//			case Types.NVARCHAR:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', hidden : " + hidden + ", width: 100, sortable: true, dataIndex: '" + column_name + "'},");
				break;
			case Types.LONGVARCHAR:
//			case Types.LONGNVARCHAR:
				break;
			//BOOLEAN
			case Types.BIT:
			case Types.BOOLEAN:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "'},");
				break;
			//日期型
			case Types.DATE:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "'},");
				break;
			case Types.TIME:	
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "'},");
				break;
			case Types.TIMESTAMP:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "'},");
				break;
			//blob
			case Types.BLOB:
				break;
			//clob
			case Types.CLOB:
				break;
			}
		}		

		config = sb.toString();
		if(config.endsWith(",")){
			config = config.substring(0, config.length()-1);
		}
		
		return config;
    }
	
	public String buildGridColumnsWithFilterConfig(String tableName) throws Exception {
    	StringBuilder sb = new StringBuilder();
    	String config = "";

		List<DbColumn> lst = dbMetaDataManager.listAllColumns(tableName);
		for(DbColumn d : lst){
			String column_name = d.getColumn_name().trim().toLowerCase();
			String column_caption = d.getComment().trim().toLowerCase();

			boolean hidden = false;
			if(column_name.equals("item_guid")
				|| column_name.equals("item_parentguid")
				|| column_name.equals("catalog_guid")
				|| column_name.equals("reportinstance_guid")
			){
				hidden = true;
			}
			
			switch(d.getData_type()){
			//整型
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.BIGINT:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "', filter: {xtype:'numberfield',filterName:'dynaBean." + column_name + "',allowDecimals:false,decimalPrecision:4,allowNegative:false,allowBlank: true} },");
				break;
			//浮点型
			case Types.FLOAT:
			case Types.REAL:
			case Types.DOUBLE:
			case Types.NUMERIC:
			case Types.DECIMAL:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "', filter: {xtype:'numberfield',filterName:'dynaBean." + column_name + "',allowDecimals:true,decimalPrecision:4,allowNegative:true,allowBlank: true} },");
				break;
			//字符串
			case Types.CHAR:
			case Types.VARCHAR:
//			case Types.NCHAR:
//			case Types.NVARCHAR:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', hidden : " + hidden + ", width: 100, sortable: true, dataIndex: '" + column_name + "', filter: {xtype:'textfield',filterName:'dynaBean." + column_name + "',allowBlank: true} },");
				break;
			case Types.LONGVARCHAR:
//			case Types.LONGNVARCHAR:
				break;
			//BOOLEAN
			case Types.BIT:
			case Types.BOOLEAN:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "', filter: {xtype:'textfield',filterName:'dynaBean." + column_name + "',allowBlank: true} },");
				break;
			//日期型
			case Types.DATE:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "', renderer: Ext.util.Format.dateRenderer('Y-m-d H:i:s'), filter: {xtype:'textfield',filterName:'dynaBean." + column_name + "',allowBlank: true} },");
				break;
			case Types.TIME:	
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "', renderer: Ext.util.Format.dateRenderer('H:i:s'), filter: {xtype:'textfield',filterName:'dynaBean." + column_name + "',allowBlank: true} },");
				break;
			case Types.TIMESTAMP:
				sb.append("\r\n{id: '" + column_name + "', header: '" + column_caption + "', width: 100, sortable: true, dataIndex: '" + column_name + "', renderer: Ext.util.Format.dateRenderer('Y-m-d H:i:s'), filter: {xtype:'textfield',filterName:'dynaBean." + column_name + "',allowBlank: true} },");
				break;
			//blob
			case Types.BLOB:
				break;
			//clob
			case Types.CLOB:
				break;
			}
		}
		
		config = sb.toString();
		if(config.endsWith(",")){
			config = config.substring(0, config.length()-1);
		}
		
		return config;
    }
	
	public String buildFormFieldsConfig(String tableName, HashMap<String, Object> dynaBean) throws Exception {
    	JSONArray jsonArray = new JSONArray();
		List<DbColumn> lst = dbMetaDataManager.listAllColumns(tableName);
		for(DbColumn d : lst){
			String column_name = d.getColumn_name().trim().toLowerCase();
			Object column_value = null;
			if(dynaBean!=null){
				column_value = dynaBean.get(column_name);
			}
			
			if(column_name.equals("item_guid")
				|| column_name.equals("item_parentguid")
				|| column_name.equals("catalog_guid")
				|| column_name.equals("reportinstance_guid")
			){
				JSONObject jsonObject = new JSONObject();				
				jsonObject.element("name", "dynaBean."+column_name).element("allowBlank", d.getNullable());
				if(d.getNullable()){
					jsonObject.element("fieldLabel", d.getComment()).element("itemCls", "readOnly");
				}else{
					jsonObject.element("fieldLabel", d.getComment()+"【必填】").element("itemCls", "required");
				}
				jsonObject.element("xtype", "textfield").element("value", column_value).element("readOnly", true);
				jsonArray.add(jsonObject);
			}
		}
		
		for(DbColumn d : lst){
			String column_name = d.getColumn_name().trim().toLowerCase();
			Object column_value = null;
			if(dynaBean!=null){
				column_value = dynaBean.get(column_name);
			}
			
			if(column_name.equals("item_guid")
				|| column_name.equals("item_parentguid")
				|| column_name.equals("catalog_guid")
				|| column_name.equals("reportinstance_guid")
			){
				continue;
			}
			
			JSONObject jsonObject = new JSONObject();				
			jsonObject.element("name", "dynaBean."+column_name).element("allowBlank", d.getNullable());
			if(d.getNullable()){
				jsonObject.element("fieldLabel", d.getComment());
			}else{
				jsonObject.element("fieldLabel", d.getComment()+"【必填】").element("itemCls", "required");
			}
			
			switch(d.getData_type()){
			//整型
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.BIGINT:
				jsonObject.element("xtype", "numberfield")
						.element("value", column_value)
						.element("allowDecimals", false)
						.element("allowNegative", false);
				break;
			//浮点型
			case Types.FLOAT:
			case Types.REAL:
			case Types.DOUBLE:
			case Types.NUMERIC:
			case Types.DECIMAL:
				jsonObject.element("xtype", "numberfield")
					.element("value", column_value)
					.element("allowDecimals", true)
					.element("decimalPrecision", 4)
					.element("allowNegative", true);
				break;
			//字符串
			case Types.CHAR:
			case Types.VARCHAR:
//			case Types.NCHAR:
//			case Types.NVARCHAR:
				jsonObject.element("xtype", "textfield").element("value", column_value);
				break;
			case Types.LONGVARCHAR:
//			case Types.LONGNVARCHAR:
				//jsonObject.element("xtype", "textfield").element("value", column_value);
				break;
			//BOOLEAN
			case Types.BIT:
			case Types.BOOLEAN:
				jsonObject.element("xtype", "checkbox")
					.element("value", column_value)
					.element("boxLabel", "选择")
					.element("checked", column_value)
					.element("inputValue", true);
				break;
			//日期型
			case Types.DATE:
				if(column_value==null){
					jsonObject.element("xtype", "datefield").element("value", column_value).element("format", "Y-m-d");
				}else{
					jsonObject.element("xtype", "datefield").element("value", DateFormatUtils.format((Date)column_value,"yyyy-MM-dd")).element("format", "Y-m-d");
				}
				break;
			case Types.TIME:	
				if(column_value==null){
					jsonObject.element("xtype", "timefield").element("value", column_value).element("format", "H:i:s");
				}else{
					jsonObject.element("xtype", "timefield").element("value", DateFormatUtils.format((Date)column_value,"HH:mm:ss")).element("format", "H:i:s");
				}
				break;
			case Types.TIMESTAMP:
				if(column_value==null){
					jsonObject.element("xtype", "datefield").element("value", column_value).element("format", "Y-m-d H:i:s");
				}else{
					jsonObject.element("xtype", "datefield").element("value", DateFormatUtils.format((Date)column_value,"yyyy-MM-dd HH:mm:ss")).element("format", "Y-m-d H:i:s");
				}
				break;
			//blob
			case Types.BLOB:
				//jsonObject.element("xtype", "trigger");
				break;
			//clob
			case Types.CLOB:
				//jsonObject.element("xtype", "textarea").element("value", column_value);
				break;
			default:
				//jsonObject.element("xtype", "textfield").element("value", column_value);
			}
			jsonArray.add(jsonObject);
		}			

		return jsonArray.toString();
    }	
	
}
